﻿"""
分数数据与CF系数处理模块
"""
# 导入需要的库
import pandas as pd
from sqlalchemy import create_engine
# 创建SQL server 数据库连接
engine = create_engine(
    "mssql+pyodbc://pereader:pereader@172.18.65.31,1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server")
# 显示所有的列 方便调试
pd.set_option('display.max_columns', None)


def get_moid(StandardPart):
    item_code = pd.read_sql(f"SELECT TOP 1 MoID FROM [dbo].[StandardPartData] WHERE "
                            f"StandardPartID='{StandardPart}' AND StandardPart_Type='一次性' "
                            f"AND DataStatus=1", engine)
    return item_code


# 根据工单获取品号
def get_item_code(MoID):
    item_code = pd.read_sql(f"SELECT top 1 ITEM_CODE FROM MO_ITEM WHERE DOC_NO='{MoID}';",engine)
    return item_code

# CF2,CF3参数获取
def get_cf(item_code,cf):
    """
    :param item_code:品号
    :param cf: 2为CF2,3为CF3
    :return: 数据状态与dataframe
    """
    cf_sel = {2:('CF2-调整系数代码', 'CF2_LEDteen', 'CF2_Code'), 3:('CF3-对标系数代码', 'CF3_Customer','CF3_Code')}
    if cf == 2 or cf == 3:
        df_CF = pd.read_sql(f"SELECT * FROM ITEM_CODE_VALUE,{cf_sel[cf][1]} WHERE ITEM_CODE='{item_code}' "
                            f"AND FEATURE_NAME='{cf_sel[cf][0]}' AND DataStatus=1 AND "
                            f"ITEM_CODE_VALUE.FEATURE_VALUE={cf_sel[cf][1]}.{cf_sel[cf][2]} "
                            "collate Chinese_PRC_CI_AS", engine)
    else:
        df_CF = pd.DataFrame()
    return df_CF

# 标准件生效数据获取
def get_standpart_data(item_code):
    """

    :param item_code:
    :return:
    """
    df_standpart_data = pd.read_sql("SELECT T1.ITEM_CODE,T1.ITEM_NAME,T2.StandardPartID,T2.StandardPartNO,T2.Current_mA AS I_y,"
                        "T2.ForwardVoltage_V AS U_y,T2.CIEx AS x_y,T2.CIEY AS y_y,T2.LuminousFlux_lm AS lm_y, "
                        "T2.Ra AS Ra_y,T2.R9 AS R9_y FROM ITEM_CODE_VALUE as T1, StandardPartData as T2 "
                        "WHERE T1.FEATURE_VALUE=T2.StandardPartID collate Chinese_PRC_CI_AS "
                        f"AND T1.FEATURE_NAME='标准件型号' AND T1.ITEM_CODE='{item_code}' AND T2.DataStatus=1;", engine)
    # 除去重复数据
    df_standpart_data.drop_duplicates(subset='StandardPartNO', keep='first', inplace=True)
    return df_standpart_data


# 测试机数据提取转换方法
def get_test_data(file_path, machine_id, pluse):
    df_test_data = pd.DataFrame()
    try:
        if machine_id in {0: '本机服务器，用于测试'}:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0,
                             usecols=['序号', '时间', 'I(A)', 'U(V)', 'P(W)',
                                      'Φ(lm)', 'Φe(mW)', '光效(lm/W)',
                                      'x', 'y', "u'", "v'", 'CCT(K)',
                                      '主波长(nm)', '峰值波长(nm)', '半峰带宽(nm)',
                                      'Ra', 'R1', 'R2', 'R3', 'R4', 'R5',
                                      'R6', 'R7', 'R8', 'R9', 'R10', 'R11', 'R12', 'R13',
                                      'R14', 'R15', 'SDCM', 'Tb'])

            df_test_data = df_test_data.rename(
               columns={'序号': 'TestNO', '时间': 'TestTime', 'I(A)': 'Current_mA', 'U(V)': 'ForwardVoltage_V', 'P(W)': 'Power_W', \
                     'Φ(lm)': 'LuminousFlux_lm', 'Φe(mW)': 'RadiantFlux_mW', '光效(lm/W)': 'LumiousEfficacy_lmPerW', \
                     'x': 'CIEx', 'y': 'CIEy', "u'": 'CIEu_1976', "v'": 'CIEv_1976', 'CCT(K)': 'CCT_K', \
                     '主波长(nm)': 'DominantWavelength_nm', '峰值波长(nm)': 'PeakWavelength_nm', '半峰带宽(nm)': 'FWHM_nm', \
                     'Ra': 'Ra', 'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', \
                     'R6': 'R6', 'R7': 'R7', 'R8': 'R8', 'R9': 'R9', 'R10': 'R10', 'R11': 'R11', 'R12': 'R12', 'R13': 'R13', \
                     'R14': 'R14', 'R15': 'R15', 'SDCM': 'SDCM', 'Tb': 'TestTemperature'})
            df_test_data['Current_mA']=df_test_data['Current_mA']*1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {1: '多谱分光机'}:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='utf_16', sep='\t', header=6, \
                             usecols=['NO', 'BIN号', 'VF', 'Φv', 'CIE-x', 'CIE-y', 'Ra', 'Tc', 'λd', 'λp', \
                                      "CIE-u'", "CIE-v'", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', \
                                      'R9', 'R10', 'R11', 'R12', 'R13', 'R14', 'R15', 'IF', 'Pow', 'SDCM', 'BIN名', '时间', \
                                      '光功率', '光效'])
            # 输出未处理的结果，方便代码调试
            df_test_data['光功率'] = df_test_data['光功率'] * 1000
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'NO': 'TestNO', 'BIN号': 'BinID', 'VF': 'ForwardVoltage_V', 'Φv': 'LuminousFlux_lm', \
                                    'CIE-x': 'CIEx', 'CIE-y': 'CIEy', \
                                    'Ra': 'Ra', 'Tc': 'CCT_K', 'λd': 'DominantWavelength_nm', 'λp': 'PeakWavelength_nm', \
                                    "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                                    'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', 'R11': 'R11', \
                                    'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'IF': 'Current_mA', \
                                    'Pow': 'Power_W', 'SDCM': 'SDCM', 'BIN名': 'BinName', '时间': 'TestTime',
                                    '光功率': 'RadiantFlux_mW', \
                                    '光效': 'LumiousEfficacy_lmPerW'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {2: '中为排测机'}:
            # 读取csv格式文件，按分光机002输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0, \
                             usecols=['  NO', 'Tc(k)', 'λd(nm)', 'λp(nm)', \
                                      '△λ', 'CIE-x', 'CIE-y', 'Ra', \
                                      'фv(lm)', 'R9', 'P(mw)', 'Vf(V)', \
                                      'I(mA)', 'P(w)', 'Efficiency(lm/w)', \
                                      'SDCM', 'BIN NO.', 'BIN name', \
                                      'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8',\
                                      'R10', 'R11', \
                                      'R12', 'R13', 'R14', 'R15', 'Time'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'  NO': 'TestNO', 'Tc(k)': 'CCT_K', 'λd(nm)': 'DominantWavelength_nm', \
                                    'λp(nm)': 'PeakWavelength_nm', '△λ': 'FWHM_nm', 'CIE-x': 'CIEx', 'CIE-y': 'CIEy',
                                    'Ra': 'Ra', \
                                    'фv(lm)': 'LuminousFlux_lm', 'R9': 'R9', 'P(mw)': 'RadiantFlux_mW',
                                    'Vf(V)': 'ForwardVoltage_V', \
                                    'I(mA)': 'Current_mA', 'P(w)': 'Power_W', 'Efficiency(lm/w)': 'LumiousEfficacy_lmPerW', \
                                    'SDCM': 'SDCM', 'BIN NO.': 'BinID', 'BIN name': 'BinName', \
                                    'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                                    'R8': 'R8', \
                                    'R10': 'R10', 'R11': 'R11', \
                                    'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'Time': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {3: '中谱连片分光机'}:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0, \
                             usecols=['No.', 'Bin号', 'Bin代号', \
                                      'IF', 'VF', 'Ф(lm)', \
                                      'η(lm/W)', 'CIE-x', 'CIE-y', \
                                      'Tc', 'Ra', 'CRI9', 'SDCM', \
                                      'P(W)', 'WL.D', 'CIE-u', 'CIE-v', \
                                      "CIE-u'", "CIE-v'", \
                                      'WL.P', 'WL.C', \
                                      'WL.H', 'Фe(mW)', 'CRI1', 'CRI2', \
                                      'CRI3', 'CRI4', 'CRI5', 'CRI6', \
                                      'CRI7', 'CRI8', 'CRI10', 'CRI11', \
                                      'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    'IF': 'Current_mA', 'VF': 'ForwardVoltage_V', 'Ф(lm)': 'LuminousFlux_lm', \
                                    'η(lm/W)': 'LumiousEfficacy_lmPerW', 'CIE-x': 'CIEx', 'CIE-y': 'CIEy', \
                                    'Tc': 'CCT_K', 'Ra': 'Ra', 'CRI9': 'R9', 'SDCM': 'SDCM', \
                                    'P(W)': 'Power_W', 'WL.D': 'DominantWavelength_nm', 'CIE-u': 'CIEu', 'CIE-v': 'CIEv', \
                                    "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'WL.P': 'PeakWavelength_nm', 'WL.C': 'ComplementaryWavelength_nm', \
                                    'WL.H': 'FWHM_nm', 'Фe(mW)': 'RadiantFlux_mW', 'CRI1': 'R1', 'CRI2': 'R2', \
                                    'CRI3': 'R3', 'CRI4': 'R4', 'CRI5': 'R5', 'CRI6': 'R6', \
                                    'CRI7': 'R7', 'CRI8': 'R8', 'CRI10': 'R10', 'CRI11': 'R11', \
                                    'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15', '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {4: '中谱单颗分光机'}:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=13, \
                             usecols=['No.', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', 'C1:VF1', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv', "C1:u'": 'CIEu_1976',
                                    "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10', 'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15', \
                                    'C1:VF1': 'ZenerVoltage_V', '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {5: '中谱一体机'}:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=15, \
                             usecols=['编号', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF1', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF1': 'ZenerVoltage_V',
                                    'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv', "C1:u'": 'CIEu_1976',
                                    "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10', 'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15',
                                    '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {22: '中谱一体机2'}:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=18, \
                             usecols=['编号', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF1', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF1': 'ZenerVoltage_V',
                                    'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv', "C1:u'": 'CIEu_1976',
                                    "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10', 'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15',
                                    '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {6: 'ZP1', 7: 'ZP2', 8: 'ZP3', 9: 'ZP4', 10: 'ZP5', 11: 'ZP6', 12: 'ZP7', 13: 'ZP8',
                            20: 'ZP9', 21: 'ZP10', 23: 'ZP12'}:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0,
                             usecols=['选择', 'No.', 'Bin号', 'Bin代号', 'VF', 'IF', 'P(W)', 'CIE-x', 'CIE-y', 'Tc',
                                      'WL.D', 'Ф(lm)', 'Ra', 'CIE-u', 'CIE-v', "CIE-u'", "CIE-v'",
                                      'WL.P', 'WL.C', 'WL.H', 'Фe(mW)', 'η(lm/W)', 'SDCM',
                                      'CRI1', 'CRI2', 'CRI3', 'CRI4', 'CRI5', 'CRI6',
                                      'CRI7', 'CRI8', 'CRI9', 'CRI10', 'CRI11',
                                      'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])


             # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'选择': 'Select', 'No.': 'TestNO',
                                                        'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    'VF': 'ForwardVoltage_V', 'IF': 'Current_mA', 'P(W)': 'Power_W', \
                                    'CIE-x': 'CIEx', 'CIE-y': 'CIEy', 'Tc': 'CCT_K', \
                                    'WL.D': 'DominantWavelength_nm', 'Ф(lm)': 'LuminousFlux_lm', 'Ra': 'Ra', \
                                    'CIE-u': 'CIEu', 'CIE-v': 'CIEv', "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'WL.P': 'PeakWavelength_nm', 'WL.C': 'ComplementaryWavelength_nm', 'WL.H': 'FWHM_nm', \
                                    'Фe(mW)': 'RadiantFlux_mW', 'η(lm/W)': 'LumiousEfficacy_lmPerW', 'SDCM': 'SDCM', \
                                    'CRI1': 'R1', 'CRI2': 'R2', 'CRI3': 'R3', 'CRI4': 'R4', 'CRI5': 'R5', 'CRI6': 'R6', \
                                    'CRI7': 'R7', 'CRI8': 'R8', 'CRI9': 'R9', 'CRI10': 'R10', 'CRI11': 'R11', \
                                    'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15', '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {14: '远方1号机'} and not pluse:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["编号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y", "CCT(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9',\
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15','Tb'])
            df_test_data = df_test_data.rename(columns={"编号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "测试时间": 'TestTime',
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                                    'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15','Tb':'TestTemperature'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {14: '远方1号机'} and pluse:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0,
                             usecols=[" 编号", "IF(mA)", "VF(V)", "P(mW)", "光通量   Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间",
                                      "色坐标 x", "色坐标 y",
                                      "色温(K)",
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9',
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度(度)', '积分时间(ms)', '脉冲宽度(ms)'])
            df_test_data = df_test_data.rename(
                columns={" 编号": 'TestNO', "IF(mA)": 'Current_mA', "VF(V)": 'ForwardVoltage_V', "P(mW)": 'Power_W',
                         "光通量   Φ(lm)": 'LuminousFlux_lm',
                         "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW',
                         "时间": 'TestTime',
                         "色坐标 x": 'CIEx', "色坐标 y": 'CIEy',
                         "色温(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm',
                         "峰值波长(nm)": 'PeakWavelength_nm',
                         "Ra": 'Ra', 'R1': 'R1',
                         'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                         'R9': 'R9', 'R10': 'R10',
                         'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                         '环境温度(度)': 'TestTemperature', '积分时间(ms)': 'U04', '脉冲宽度(ms)': 'U05'})
            # df['Current_mA'] = df['Current_mA'] * 1000
            df_test_data['Power_W'] = round(df_test_data['Power_W'] / 1000, 4)
            df_test_data['TestTime'] = df_test_data['TestTime'].apply(lambda x: x[:-3] + ":" + x[-2:])

        elif machine_id in {15: '远方2号机'}:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y", "Tc(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9',\
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15','环境温度'])
            df_test_data = df_test_data.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "测试时间": 'TestTime',
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "Tc(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                                    'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15','环境温度':'TestTemperature'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {16: '远方3号机'}:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间", "x", "y", "CCT(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9',\
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15'])
            df_test_data = df_test_data.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "时间": 'TestTime',
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8',
                                    'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_id in {17: '中为7号机',18: '中为6号机',19: '中为5号机'}:
            df_test_data = pd.read_excel(io=file_path, sheet_name=0, header=0, \
                               usecols=["序号", "电流(mA)", "电压VF(V)", "功率(w)", "光通量(lm)", "光功率(mW)", "光效率(lm/w)", \
                                        "CIE-X", "CIE-Y", "BIN号", "色温(K)", "显色指数", "R9", "SDCM", "主波长(nm)", \
                                        "峰波长(nm)", "时间", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9','R10', \
                                        'R11', 'R12', 'R13', 'R14', 'R15'])
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(
                columns={"序号": 'TestNO', "电流(mA)": 'Current_mA', "电压VF(V)": 'ForwardVoltage_V', "功率(w)": 'Power_W', \
                         "光通量(lm)": 'LuminousFlux_lm', "光功率(mW)": 'RadiantFlux_mW', "光效率(lm/w)": 'LumiousEfficacy_lmPerW', \
                         "CIE-X": 'CIEx', "CIE-Y": 'CIEy', "BIN号": 'BinName', "色温(K)": 'CCT_K', "显色指数": 'Ra',
                         "SDCM": 'SDCM', \
                         "主波长(nm)": 'DominantWavelength_nm', "峰波长(nm)": 'PeakWavelength_nm', "时间": 'TestTime', \
                         'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                         'R10': 'R10', 'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])
        else:
            print('设备ID有误')
        return df_test_data

    except Exception as e:
        print(e)
        return df_test_data

# 数据加系数
def cal_coef(standpart_data, test_data):
    """
    :param standpart_data:
    :param test_data:
    :return:
    """
    df_cal_data1 = pd.DataFrame()
    test_data = test_data.rename(
        columns={'TestNO':'StandardPartNO','Current_mA':'I_x','ForwardVoltage_V':'U_x','LuminousFlux_lm':'lm_x',
                 'CIEx':'x_x','CIEy':'y_x', 'Ra':'Ra_x', 'R9':'R9_x'})
    test_data1 = test_data.loc[:, ['StandardPartNO', 'TestTime', 'I_x', 'U_x', 'lm_x', 'x_x', 'y_x', 'Ra_x', 'R9_x']]
    df_cal_data = pd.merge(standpart_data, test_data1, how='inner', on='StandardPartNO')

    if not df_cal_data.empty:
        # 生产参数列表
        pras = [['dU', 'U_y', 'U_x'], ['dx', 'x_y', 'x_x'],
                ['dy', 'y_y', 'y_x'], ['dRa', 'Ra_y', 'Ra_x'],
                ['dR9', 'R9_y', 'R9_x']]
        for i in pras:
            df_cal_data[i[0]] = df_cal_data[i[1]] - df_cal_data[i[2]]

        df_cal_data['dlm'] = df_cal_data['lm_y'] / df_cal_data['lm_x']
        df_cal_data['dI'] = (abs(df_cal_data['I_y'] - df_cal_data['I_x']))/df_cal_data['I_y']
        df_cal_data1 = df_cal_data.loc[:, ['ITEM_CODE', 'StandardPartNO', 'dU', 'dx', 'dy', 'dRa', 'dR9', 'dlm', 'dI']]
        pras = [['dU', 'U_y', 'U_x'], ['dx', 'x_y', 'x_x'],
                ['dy', 'y_y', 'y_x'], ['dRa', 'Ra_y', 'Ra_x'],
                ['dR9', 'R9_y', 'R9_x']]
        # 对各个系数进行求平均值
        for i in pras:
            df_cal_data1.loc['校准系数', [i[0]]] = df_cal_data[i[0]].mean()
        df_cal_data1.loc['校准系数', 'dlm'] = df_cal_data['dlm'].mean()
        df_cal_data1.loc['校准系数', 'dI'] = df_cal_data['dI'].mean()
        df_cal_data1.loc['校准系数', 'ITEM_CODE'] = df_cal_data.loc[0,'ITEM_CODE']
        # print(df_cal_data1)
        return df_cal_data1
    else:
        return df_cal_data1

#验证校准是否有效并计算最终系数，在原有系数上面叠加CF2和CF3
def cal_end_coef(coef_data, cf2_data, cf3_data):
    """

    :param coef_data: 中间系数，数据类型为字典
    :param cf2: cf2数据
    :param cf3: cf3数据
    :return: df_end_coef 最终系数数据
    """
    # 获取未加cf2，cf3的校准系数
    coef = pd.Series(coef_data)
    # 创建加cf2，cf3的校准系数，并进行运算
    end_coef = coef.copy()
    pras = [['dU', 'ForwardVoltage_offset'], ['dx', 'CIEx_offset'],
            ['dy', 'CIEy_offset'], ['dRa', 'CRI_offset'],
            ['dR9', 'R9_offset']]
    # cf2 = cf2_data.loc[0]
    # cf3 = cf3_data.loc[0]
    for i in pras:
        # end_coef[i[0]] = coef[i[0]] + cf2[i[1]] + cf3[i[1]]
        end_coef[i[0]] = coef[i[0]] + cf2_data.loc[0,i[1]] + cf3_data.loc[0,i[1]]
    end_coef['dlm'] = coef['dlm'] * cf2_data.loc[0, 'LuminousFlux_gain'] * cf3_data.loc[0, 'LuminousFlux_gain']
    df_end_coef = end_coef.to_frame().T.rename(index={0: "最终系数"})
    # result = fail_result.append(end_coef.to_frame().T.rename(index={0: "最终系数"}))
    return df_end_coef


# 测试数据加系数运算,统一按y=kx+b进行线性计算
def cal_end_data(test_data, cf_data):
    """
    :param test_data:测试数据
    :param cf_data:cf2 或cf 3
    :return:
    """
    pras = [['ForwardVoltage_V', 'ForwardVoltage_gain', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_gain', 'CIEx_offset'],
          ['CIEy', 'CIEy_gain', 'CIEy_offset'], ['LuminousFlux_lm', 'LuminousFlux_gain', 'LuminousFlux_offset'],
          ['Ra', 'CRI_gain', 'CRI_offset'], ['R9', 'R9_gain', 'R9_offset']]
    cal_data = test_data.copy()
    # 全部安装y=kx+b进行线性计算
    for i in pras:
        cal_data[i[0]] = test_data.loc[:, [i[0]]].apply(lambda x: cf_data.loc[0, i[1]] * x + cf_data.loc[0, i[2]])
    return cal_data



if __name__ == '__main__':
    # print(get_item_code('5101-20060005'))
    # print(get_cf('10105030107',2))
    # print(get_cf('10105030107', 3))
    # print(get_standpart_data('10105030107'))
    print(get_test_data('1.xls',14))
    print(cal_coef(get_standpart_data('10105030107'), get_test_data('1.xls',14)))
    # print(cal_end_coef(cal_coef(get_standpart_data('10105030107'), get_test_data('对标20200521-2.xls',0)).loc['校准系数'].to_dict(),
    #              get_cf('10105030107',2),get_cf('10105030107', 3)))
    # print(cal_end_data(get_test_data('对标20200521-2.xls',0),get_cf('10105030107',3)))



   




